package com.mms.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.entity.Configure;
import com.mms.entity.ConfigureId;
import com.mms.entity.Device;
import com.mms.entity.DeviceCount;
import com.mms.entity.Meeting;
import com.mms.entity.MeetingRoom;
import com.mms.util.HibernateUtil;

public class ApplyMeetingDao {

    public List<MeetingRoom> getMeetingRoomsByTimeAndCapacity(String startTime, String endTime, int capacity) {

        List<Object[]> objs;
        List<MeetingRoom> meetingRooms = new ArrayList<MeetingRoom>();
        Session session = HibernateUtil.getSession();
        try {
            String sql = "select * from meeting_room where capacity >= ? and state = ? and mr_name not in ( select mr_name from meeting natural join meeting_room where (start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) or (start_time > ? and end_time < ?) )";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            query.setInteger(0, capacity);
            query.setInteger(1, 0);
            query.setString(2, startTime);
            query.setString(3, startTime);
            query.setString(4, endTime);
            query.setString(5, endTime);
            query.setString(6, startTime);
            query.setString(7, endTime);
            objs = query.list();
            for (Object[] objects : objs) {
                MeetingRoom meetingRoom = new MeetingRoom();
                meetingRoom.setMrId(Integer.parseInt(objects[0].toString()));
                meetingRoom.setCapacity(Short.parseShort(objects[1].toString()));
                meetingRoom.setState(Integer.parseInt(objects[2].toString()));
                meetingRoom.setMrName(objects[3].toString());
                meetingRooms.add(meetingRoom);
            }
            transaction.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            session.close();
        }
        return meetingRooms;

    }

    public List<Device> getDevicesByTimeAndNumber(String startTime, String endTime) {

        List<Object[]> objs;
        List<Device> devicesList = new ArrayList<Device>();
        Session session = HibernateUtil.getSession();
        try {
            String sql = "select * from device where d_id not in ( select d_id from configure natural join meeting where (start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) or (start_time > ? and end_time < ?)  )";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            query.setString(0, startTime);
            query.setString(1, startTime);
            query.setString(2, endTime);
            query.setString(3, endTime);
            query.setString(4, startTime);
            query.setString(5, endTime);
            objs = query.list();
            for (Object[] objects : objs) {
                Device device = new Device();
                device.setDId(Integer.parseInt(objects[0].toString()));
                device.setDName(objects[1].toString());
                devicesList.add(device);
            }
            transaction.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            session.close();
        }
        return devicesList;

    }

    public List<DeviceCount> getDevicesCountByTimeAndNumber(String startTime, String endTime) {
        List<Object[]> objs;
        List<DeviceCount> deviceCounts = new ArrayList<DeviceCount>();
        Session session = HibernateUtil.getSession();
        try {
            String sql = "select  count(*),d_name from device where d_id not in ( select d_id from configure natural join meeting where (start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) or (start_time > ? and end_time < ?) ) group by d_name";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            query.setString(0, startTime);
            query.setString(1, startTime);
            query.setString(2, endTime);
            query.setString(3, endTime);
            query.setString(4, startTime);
            query.setString(5, endTime);
            objs = query.list();
            for (Object[] objects : objs) {
                DeviceCount dCount = new DeviceCount();
                dCount.setCount(Integer.parseInt(objects[0].toString()));
                dCount.setDeviceName(objects[1].toString());
                deviceCounts.add(dCount);
            }
            transaction.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            session.close();
        }
        return deviceCounts;
        
    }
    
    public boolean addMeeting(Meeting meeting){
        Session session=HibernateUtil.getSession();
           try {
               Transaction transaction=session.beginTransaction();
               session.save(meeting);
               transaction.commit();
               return true;
           } catch (Exception e) {
               e.printStackTrace();
               return false;
          }
           finally{
               session.close();
           }
   }
    
    public boolean bind(Configure configure){
        Session session = HibernateUtil.getSession(); 
        try{ 
        Transaction transaction=session.beginTransaction();
        session.save(configure); 
        transaction.commit();
        return true; 
        }catch(Exception ex){ 
            ex.printStackTrace();
        return false; 
        }finally{ 
        session.close(); 
        } 
   }
    
    public boolean isConfigureExist(ConfigureId configureId){
        boolean isExist = false;
           List<BigInteger> result ;
           int num = 0;
           Session session = HibernateUtil.getSession(); 
           try{ 
               String sql = "select count(*) from configure where d_id = ? and topic = ?";              
               Transaction transaction=session.beginTransaction();
               Query query=session.createSQLQuery(sql);
               query.setInteger(0,configureId.getDId());
               query.setString(1,configureId.getTopic());
               result=query.list();
               num=Integer.parseInt(result.get(0).toString());
               if (num>0)  isExist=true;
               transaction.commit();
           }catch(Exception ex){ 
               ex.printStackTrace(); 
           }finally{ 
           session.close(); 
           }
           return isExist;
   }
    
    
    public boolean isTopicExist(String topic) {
           boolean isExist = false;
           List<BigInteger> result ;
           int num = 0;
           Session session = HibernateUtil.getSession(); 
           try{ 
               String sql = "select count(*) from meeting where topic = ?";
               Transaction transaction=session.beginTransaction();
               Query query=session.createSQLQuery(sql);
               query.setString(0, topic);
               result=query.list();
               num=Integer.parseInt(result.get(0).toString());
               if (num>0) {
                   isExist=true;
               }
               transaction.commit();
           }catch(Exception ex){ 
               ex.printStackTrace(); 
           }finally{ 
           session.close(); 
           }
           return isExist;
    }
    
    public boolean isTimeLegal(String startTime,String endTime,int meetingRoomID) {
           boolean isLegal = true;
           List<BigInteger> result ;
           int num = 0;
           Session session = HibernateUtil.getSession(); 
           try{ 
               String sql = "select count(*) from meeting natural join meeting_room where mr_id = ? and ((start_time <= ? and end_time > ?) or  (start_time < ? and end_time >= ?) or (start_time >= ? and end_time <=?) )";
               Transaction transaction=session.beginTransaction();
               Query query=session.createSQLQuery(sql);
               query.setInteger(0, meetingRoomID);
               query.setString(1, startTime);
               query.setString(2, startTime);
               query.setString(3, endTime);
               query.setString(4, endTime);
               query.setString(5, startTime);
               query.setString(6, endTime);
               result=query.list();
               num=Integer.parseInt(result.get(0).toString());
               if (num>0) {
                   isLegal=false;
               }
               transaction.commit();
           }catch(Exception ex){ 
               ex.printStackTrace(); 
           }finally{ 
           session.close(); 
           }
           return isLegal;
    }
    
    public List<Meeting> getMeetingsByStartTimeAndAddress(int meetingRoomID,String startTime) {
        List<Object[]> objs;
        List<Meeting> meetings = new ArrayList<Meeting>();
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select start_time,end_time,topic from meeting where start_time like ? and mr_id = ?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setString(0, startTime+"%");
            query.setInteger(1,meetingRoomID);
            objs=query.list();
            for (Object[] objects : objs) {
                Meeting meeting = new Meeting();
                meeting.setStartTime(Timestamp.valueOf(objects[0].toString()));
                meeting.setEndTime(Timestamp.valueOf(objects[1].toString()));
                meeting.setTopic(objects[2].toString());
                meetings.add(meeting);
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return meetings;
        
    }
    
    
    public List<MeetingRoom> getMeetingRooms() {
        Session session=HibernateUtil.getSession();
        List<Object[]> objs;
        List<MeetingRoom> meetingRooms =new ArrayList<MeetingRoom>();
        try {
            String sql="select * from meeting_room";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            objs=query.list();
            for (Object[] objects : objs) {
                MeetingRoom meetingRoom = new MeetingRoom();
                meetingRoom.setMrId(Integer.parseInt(objects[0].toString()));
                meetingRoom.setCapacity(Short.parseShort(objects[1].toString()));
                meetingRoom.setState(Integer.parseInt(objects[2].toString()));
                meetingRoom.setMrName(objects[3].toString());
                meetingRooms.add(meetingRoom);
            }
            transaction.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return meetingRooms;
    }


}
